USE master
GO
SET NOCOUNT ON
GO

BEGIN
DECLARE @device_directory NVARCHAR(520)
SELECT @device_directory =
SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM master.dbo.sysaltfiles
WHERE dbid = 1 AND fileid = 1

if exists (select * from sysdatabases where name='SampleDatabase')
drop database SampleDatabase
/****** Object:  Database [SampleDatabase]    Script Date: 03/02/2013 15:42:04 ******/
EXECUTE (N'CREATE DATABASE [SampleDatabase] ON  PRIMARY 
( NAME = N''SampleDatabase'', FILENAME = N''' + @device_directory + N'\SampleDatabase.mdf'' )
 LOG ON 
( NAME = N''SampleDatabase_log'', FILENAME = N''' + @device_directory+'\SampleDatabase_log.ldf'')');
END
GO
EXEC sp_dbcmptlevel 'SampleDatabase', 90
ALTER DATABASE [SampleDatabase] SET RECOVERY SIMPLE
GO

/****** Object:  Table [dbo].[User] ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON
use [SampleDatabase]
CREATE TABLE [dbo].[User](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Birthday] [datetime] NOT NULL
) ON [PRIMARY]

GO


/****** Object:  Table [dbo].[Sample] ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON
use [SampleDatabase]
CREATE TABLE [dbo].[Sample](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	[Description] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE PROCEDURE [dbo].[GetLastFiveSamples]
	@page int = 0,
	@limit int = 20
AS
	SELECT TOP 5 Id, Title, [Description] from Sample
	order by Id desc
RETURN 0;

GO
CREATE PROCEDURE [dbo].[GetListOfRecipes]
	@page int = 0,
	@limit int = 20,
	@Keyword nvarchar(52) = ''
AS
	DECLARE @query nvarchar(2000);
	set @query = 'SELECT Id, Title, [Description] from Sample where 1 = 1';
	
	if (@Keyword != '')
		Set @query = @query + ' and ([Description] like ''%'+@Keyword+'%'')' 

		print @query
	EXECUTE sp_executesql @query
RETURN 0;

GO

INSERT INTO [dbo].[Sample] ([Title] ,[Description]) VALUES ('title1', 'Description1');
INSERT INTO [dbo].[Sample] ([Title] ,[Description]) VALUES ('title2', 'Description2');
INSERT INTO [dbo].[Sample] ([Title] ,[Description]) VALUES ('title3', 'Description3');
INSERT INTO [dbo].[Sample] ([Title] ,[Description]) VALUES ('title4', 'Description4');
INSERT INTO [dbo].[Sample] ([Title] ,[Description]) VALUES ('title5', 'Description5');

INSERT INTO [dbo].[User] ([FirstName] ,[LastName] ,[Birthday]) VALUES ('Jeff', 'Smidth', '14-10-1980');
INSERT INTO [dbo].[User] ([FirstName] ,[LastName] ,[Birthday]) VALUES ('Gill', 'Bates', '20-02-1976');
INSERT INTO [dbo].[User] ([FirstName] ,[LastName] ,[Birthday]) VALUES ('Steeve', 'Salmer', '25-05-1984');
INSERT INTO [dbo].[User] ([FirstName] ,[LastName] ,[Birthday]) VALUES ('Mickael', 'Bordan', '06-12-1982');
GO

